﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Reflection;
using crm_library.DataAccess.ObjectReps;
using System.Runtime.Serialization;
using crm_library.Extenstions;

namespace crm_library.DataAccess.TableReps
{
	[DataContractAttribute]
	public abstract class ObjectRepresentation
	{
		[DataMember]
		public string TableName { get; private set; }
		private string SqlTableName { get { return '[' + TableName + ']'; } }
		[DataMember]
		public string ObjectName { get; private set; }

		public ObjectRepresentation(string tableName, string objectName)
		{
			TableName = tableName;
			ObjectName = objectName;
		}

		#region database
		public abstract int GetPKValue();
		public abstract void SetPKValue(int pk);
		public abstract int GetNextPKValue();
		public abstract void Create();
		public void Load() { Load(GetPKValue()); }
		public abstract void Load(int pk);
		public abstract void Update();
		public void Delete() { Delete(GetPKValue()); }
		public abstract void Delete(int pk);


		public string getPKFieldName()
		{
			foreach (var property in PropertyInfoList)
			{
				var dbAttributes = (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true);

				foreach (var attr in dbAttributes)
				{
					if (attr.IsPK) return attr.ColumnName;
				}
			}
			throw new Exception("Table " + TableName + " has no PK set.");
		}


		public SqlCommand ToCreateCommand()
		{
			List<string> fields = new List<string>();
			List<string> values = new List<string>();

			foreach (var property in PropertyInfoList)
			{
				foreach (var attr in (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true))
				{
					if (!attr.IsPK)
					{
						fields.Add(attr.ColumnName);
						object value = property.GetValue(this, null);

						if (value != null)
						{
							values.Add("'" + value.ToString().Trim() + "'");
						}
						else
						{
							values.Add("''");
						}
					}
					break;
				}
			}


			var statement = new StringBuilder();
			statement.Append("INSERT INTO " + SqlTableName + " ([");
			statement.Append(String.Join("], [", fields.ToArray()) + "]) ");

			statement.Append("VALUES (");
			statement.Append(String.Join(", ", values.ToArray()) + ")");

			return new SqlCommand(statement.ToString());
		}
		public SqlCommand ToReadCommand()
		{
			List<string> fields = new List<string>();
			foreach (var property in PropertyInfoList)
			{
				foreach (var attr in (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true))
				{
					fields.Add(attr.ColumnName);
					break;
				}
			}

			var statement = new StringBuilder();
			statement.Append("SELECT [");
			statement.Append(String.Join("], [", fields.ToArray()) + "] ");
			statement.Append("FROM " + SqlTableName);
			statement.Append(" WHERE " + getPKFieldName() + " = " + GetPKValue());

			return new SqlCommand(statement.ToString());
		}
		public SqlCommand ToReadAllCommand()
		{
			List<string> fields = new List<string>();
			foreach (var property in PropertyInfoList)
			{
				foreach (var attr in (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true))
				{
					fields.Add(attr.ColumnName);
					break;
				}
			}

			var statement = new StringBuilder();
			statement.Append("SELECT [");
			statement.Append(String.Join("], [", fields.ToArray()) + "] ");
			statement.Append("FROM " + SqlTableName);

			return new SqlCommand(statement.ToString());
		}
		public SqlCommand ToUpdateCommand()
		{

			List<string> fields = new List<string>();
			List<string> values = new List<string>();

			foreach (var property in PropertyInfoList)
			{
				foreach (var attr in (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true))
				{
					if (!attr.IsPK)
					{
						fields.Add(attr.ColumnName);

						object value = property.GetValue(this, null);

						Type type = property.PropertyType;



						if (IsNumericType(type))
						{
							values.Add(value.ToString().Trim());
						}
						else if (IsDateTime(type))
						{
							var dateTime = (DateTime)value;

							values.Add(dateTime.ToSqlDateTime());
						}
						else if (value != null)
						{
							values.Add(value.ToString().Trim());
						}
						else
						{
							values.Add("");
						}

					}
					break;
				}
			}

			var statement = new StringBuilder();
			statement.Append("UPDATE " + SqlTableName + " SET ");

			var parameters = new List<SqlParameter>();

			const string setFormat = ("{0}=@{0}");

			for (int i = 0; i < fields.Count; i++)
			{
				statement.Append(String.Format(setFormat, fields[i]));
				if (i < fields.Count - 1) statement.Append(", ");
				parameters.Add(new SqlParameter('@' + fields[i], values[i]));
			}

			statement.Append(" WHERE [" + getPKFieldName() + "] = " + GetPKValue());

			var cmd = new SqlCommand(statement.ToString());
			cmd.Parameters.AddRange(parameters.ToArray());

			return cmd;
		}
		public SqlCommand ToDeleteCommand()
		{

			var statement = new StringBuilder();
			statement.Append("DELETE FROM " + SqlTableName);
			statement.Append(" WHERE [" + getPKFieldName() + "] = " + GetPKValue());

			return new SqlCommand(statement.ToString());
		}



		public IEnumerable<PropertyInfo> PropertyInfoList
		{
			get
			{
				return GetType().GetProperties(BindingFlags.Public | BindingFlags.SetProperty | BindingFlags.Instance).ToList();
			}
		}

		public List<string> DBColumnNames
		{
			get
			{
				List<string> columns = new List<string>();

				foreach (var property in PropertyInfoList)
				{
					var dbAttributes = (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					columns.Add(dbAttributes[0].ColumnName);
				}
				return columns;
			}
		}

		public List<string> ListingColumnNames
		{
			get
			{
				List<string> columns = new List<string>();

				foreach (var property in PropertyInfoList)
				{
					var dbAttributes = (ListingFieldAttribute[])property.GetCustomAttributes(typeof(ListingFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					columns.Add(dbAttributes[0].DisplayText);
				}
				return columns;
			}
		}

		public List<object> DbValues
		{
			get
			{
				List<object> values = new List<object>();

				foreach (var property in PropertyInfoList)
				{
					var dbAttributes = (DataFieldAttribute[])property.GetCustomAttributes(typeof(DataFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					values.Add(property.GetValue(this, null));
				}
				return values;
			}
		}

		public List<object> ListingValues
		{
			get
			{
				List<object> values = new List<object>();

				foreach (var property in PropertyInfoList)
				{
					var dbAttributes = (ListingFieldAttribute[])property.GetCustomAttributes(typeof(ListingFieldAttribute), true);

					if (!dbAttributes.Any()) continue;

					values.Add(property.GetValue(this, null));
				}
				return values;
			}
		}

		#endregion database

		#region Pages

		public string GetCreatePageName()
		{
			return String.Format("Create{0}.aspx", this.ObjectName);
		}

		public string GetEditPageName()
		{
			return String.Format("Edit{0}.aspx", this.ObjectName);
		}

		public string GetViewPageName()
		{
			return String.Format("View{0}.aspx", this.ObjectName);
		}

		#endregion Pages

		#region utilities
		public abstract ListItem ToListItem();


		internal static bool IsDateTime(Type typ)
		{
			return (Type.GetTypeCode(typ) == TypeCode.DateTime);
		}

		internal static bool IsNumericType(Type typ)
		{
			switch (Type.GetTypeCode(typ))
			{
				case TypeCode.Decimal:
				case TypeCode.Double:
				case TypeCode.Int16:
				case TypeCode.Int32:
				case TypeCode.Int64:
				case TypeCode.SByte:
				case TypeCode.Single:
				case TypeCode.UInt16:
				case TypeCode.UInt32:
				case TypeCode.UInt64:
					return true;
			};
			return false;
		}



		#endregion utilities
	}
}
